package dao;

import bean.Area;
import bean.People;
import bean.Salary;
import util.JDBCutil;
import util.SQLUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class PeopleDao {
    private Connection connection = JDBCutil.getConnection();

    public int addPeople(People people) {
        int row = 0;
        String sql = "INSERT INTO people (name, age, sex, area_id,salary_id) VALUES (?,?,?,?,?);";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, people.getName());
            ps.setString(2, people.getAge());
            ps.setString(3, people.getSex());
            ps.setInt(4, people.getArea().getId());
            ps.setInt(5, people.getSalary().getId());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    public List<People> peopleList() {
        List<People> list = new ArrayList<>();

        String sql = "SELECT * FROM people,area,salary WHERE people.area_id=area.id AND people.salary_id=salary.id";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                People people = new People();
                people.setId(set.getInt("people.id"));
                people.setName(set.getString("people.name"));
                people.setSex(set.getString("people.sex"));
                people.setAge(set.getString("people.age"));
                Salary salary = new Salary();
                salary.setMoney(set.getInt("salary.money"));
                people.setSalary(salary);
                Area area = new Area();
                area.setName(set.getString("area.name"));
                people.setArea(area);
                list.add(people);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int deletePeople(int id) {
        int row = 0;
        String sql1 = "DELETE FROM goods WHERE goods.people_id=?";
        SQLUtil.deleteInfo(sql1, id);
        String sql2 = "DELETE FROM task WHERE task.people_id=?";
        SQLUtil.deleteInfo(sql2, 1);
        String sql = "DELETE FROM people WHERE id=?";
        row = SQLUtil.deleteInfo(sql, id);
        return row;
    }

    public int updatePeople(People people) {
        int row = 0;
        String sql = "UPDATE people SET name = ?,age=?,sex=?,area_id=? WHERE id=? ;";

        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, people.getName());
            ps.setString(2, people.getAge());
            ps.setString(3, people.getSex());
            ps.setInt(4, people.getArea().getId());
            ps.setInt(5, people.getId());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    public People getPeople(int id) {
        People people = new People();
        String sql = "SELECT * FROM people WHERE id=?";

        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet set = ps.executeQuery();
            while (set.next()) {

                people.setName(set.getString("name"));
                people.setAge(set.getString("age"));
                people.setSex(set.getString("sex"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("people = " + people.toString());
        return people;
    }
}
